import java.sql.*;
import java.io.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;

/*************************************************************************
 * Filename: Script.Java
 * Company:  1186 Entertainment
 * Last Modified: 3-20-06
 *
 * Description:
 * This class provides a script runner for an oracle database
 * The class can read in an ASCII text file and then execute the
 * sequential SQL statements with the database
 *
 * TODO:
 *
 * Change Log:
 *
 * @author Ben Johnson
 *
 *************************************************************************/
public class Script
{

	/**
	 * Executes the SQL statements found in "file".<br>
	 * This method breaks the provided SQL file in executable statements
	 * on a semicolon on the end of the line, like in:<br>
	 * <code>SELECT * FROM table<br>...<br>ORDER BY id<b>;</b></code><br>
	 * The semicolon will be skipped (causes error in JDBC), but it's
	 * needed by the command line clients;
	 *
	 * @param connection the Connection object to the Oracle database
	 * @param file The ASCII script file with the Oracle syntax
	 * @param log A log file to display the actions performed
	 *
	 */
	public void executeScript(Connection connection, File file, PrintWriter log) throws IOException, SQLException
	{
		log.println("Reading from file " + file.getAbsolutePath());
		System.out.println("Reading from file " + file.getAbsolutePath());

		Statement stmt = connection.createStatement();
		BufferedReader reader = new BufferedReader(new FileReader(file));
		StringBuffer sqlBuf = new StringBuffer();
		String line;
		boolean statementReady = false;
		int count = 0;

		while ((line = reader.readLine()) != null) { //loop thru lines in the file
			// different continuation for oracle and postgres
			line = line.trim();

			if(line.indexOf("#") != -1)line = line.substring(0,line.indexOf("#"));

			if (line.equals("--/exe/--"))   //execute finished statement for postgres
			{
				sqlBuf.append(' ');
				statementReady = true;
			}
			else if (line.equals("/")) //execute finished statement for oracle
			{
				sqlBuf.append(' ');
				statementReady = true;
			}
			else if (line.startsWith("#") || line.startsWith("--") || line.length() == 0)  // comment or empty
			{
				continue;
			}
			else if (line.endsWith(";"))
			{
				sqlBuf.append(' ');
				sqlBuf.append(line.substring(0, line.length() - 1) + "\n");
				statementReady = true;
			}
			else
			{
				sqlBuf.append(' ');
				sqlBuf.append(line + "\n");
				statementReady = false;
			}
			if (statementReady) {
				if (sqlBuf.length() == 0) continue;
				System.out.println("exe: " + sqlBuf.toString());
				try{
					stmt.executeQuery(sqlBuf.toString().replaceAll("\n",""));
				}
				catch(SQLException e)
				{
					System.out.println(e);
				}
				count ++;
				sqlBuf.setLength(0);
			}
		}

		log.println("" + count + " statements processed");
		log.println("Import done sucessfully");
		System.out.println("" + count + " statements processed");
		System.out.println("Import done sucessfully");

    }//end executeScript()

}//end of class